<?php

namespace app\index\model;

use  think\Db;

class ShoponstockModel
 {
     //用户列表
     static public function getList($param, $titles = null, $function = null)
     {
        $where=[];$condition=[];$country='';$shop_out='';$shop='';
        
        //调拨单处理 ----start-------
        $db=Db::table('allocate_order_detail')->alias('d')->join('allocate_order p' ,'p.id = d.allocate_order_id','left')->field("p.type,GROUP_CONCAT(concat(p.allocate_order_sn,'_',d.sku))")->group('type');
        //采销单处理
         $dbPurchase=Db::table('purchase_order_detail')->alias('d')->join('purchase_order p' ,'p.id = d.po_id','left')->field("p.type,concat(p.bs_code,'_',d.sku)");
         if (!empty($param['sku'])) {
             $where['d.sku']=$param['sku'];
             $condition['d.sku']=$param['sku'];
         }
         if (!empty($param['warehouse_id_out'])) {
             $where['p.base_warehouse_id']=$param['warehouse_id_out'];
             $condition['p.ship_warehouse_id']=$param['warehouse_id_out'];
         }
         if (!empty($param['warehouse_id_rec'])) {
             $where['p.base_warehouse_id_in']=$param['warehouse_id_rec'];
             $condition['p.receipt_warehouse_id']=$param['warehouse_id_rec'];
         }
         if (!empty($param['code'])) {
             $where['p.allocate_order_sn']=$param['code'];
             $condition['p.bs_code']=$param['warehouse_id_out'];
         }
        $result=$db->where($where)->column("GROUP_CONCAT(concat(p.allocate_order_sn,'_',d.sku))",'type');//1.国家仓到店仓、店仓到店仓
        $countryArr=isset($result[1]) ? explode(",",$result[1]):'';
        $shopArr=isset($result[2]) ? explode(",",$result[2]):'';
        $purchaseArr=$dbPurchase->where($condition)->column("concat(p.bs_code,'_',d.sku)");
        //店仓入库数据
        $warehouse=Db::table('shop_warehouse_in_detail')->alias('cd')->join('shop_warehouse_in c' ,'cd.shop_warehouse_in_id = c.id','left')
                 ->field('quantity_in warequantity,c.create_time as waredate,business_sn,base_warehouse_id receipt_warehouse_id,concat(business_sn,"_",sku) sncode')
                 ->where('status','=','1')->where('type','in',[1,2])->buildSql();

        if($countryArr){  //获取国家仓出库信息
            $country_out=Db::table('warehouse_country_out_detail')->alias('cd')->join('warehouse_country_out c' ,'cd.warehouse_country_out_id = c.id','left')
                 ->field('`quantity`,`date`,"调拨" allocate_type,"国家仓",`bs_code`,`sku`,`sku_name`,`price`,`ship_warehouse_id`,concat(`bs_code`,"_",`sku`) as code_sku')->where('status','=','20')->where('type','=','10')->buildSql();
            $country=Db::table($country_out)->alias('o')->join("$warehouse w",'w.sncode=o.code_sku','left');
        }
        if($purchaseArr){  //获取国家仓出库信息（采销）
            $purchase_out=Db::table('warehouse_country_out_detail')->alias('cd')->join('warehouse_country_out c' ,'cd.warehouse_country_out_id = c.id','left')
                 ->field('`quantity`,`date`,"采销" allocate_type,"国家仓",`bs_code`,`sku`,`sku_name`,`price`,`ship_warehouse_id`,concat(`bs_code`,"_",`sku`) as code_sku')->where('status','=','20')->where('type','=','30')->buildSql();
            $purchase=Db::table($purchase_out)->alias('c')->join("$warehouse w",'w.sncode=c.code_sku','left');
        }      
        if($shopArr){  //获取店仓出库信息
            $shop_out=Db::table('shop_warehouse_out')->field('`quantity`,`create_time` date,"调拨" allocate_type,"店仓",`business_sn` bs_code,`sku`,`sku_name`,`price`,`base_warehouse_id` ship_warehouse_id,concat(business_sn,"_",sku) as code_sku')
                    ->where('status','=','1')->where('type','=','1')->buildSql();
            $shop=Db::table($shop_out)->alias('s')->join("$warehouse w",'w.sncode=s.code_sku','left');
        }
        if(!empty($countryArr) && !empty($shopArr)){  //国家仓和店仓出库都存在的话
            $country=$country->buildSql();
            $shop=$shop->where('s.code_sku','in',$shopArr)->buildSql();
            if(!empty($purchaseArr)){
                $purchase=$purchase->where('c.code_sku','in',$purchaseArr)->buildSql();
                $sql=Db::table($country)->alias('o')->union($shop)->union($purchase)->where("o.code_sku",'in',$countryArr);  
            }else{
                $sql=Db::table($country)->alias('o')->union($shop)->where("o.code_sku",'in',$countryArr);  
            }
              
        }else if(empty($countryArr) && !empty($shopArr)){//店仓出库存在
            $shop=$shop->buildSql();
            if(!empty($purchaseArr)){
                $purchase=$purchase->where('c.code_sku','in',$purchaseArr)->buildSql();
                 $sql=Db::table($shop)->alias('s')->union($purchase)->where("s.code_sku",'in',$shopArr);  
            }else{
                 $sql=Db::table($shop)->alias('s')->where("s.code_sku",'in',$shopArr);
            }
        }else if(!empty($countryArr) && empty($shopArr)){  //国家仓出库存在
            $country=$country->buildSql();
            if(!empty($purchaseArr)){
                $purchase=$purchase->where('c.code_sku','in',$purchaseArr)->buildSql();
                $sql=Db::table($country)->alias('o')->union($purchase)->where("o.code_sku",'in',$countryArr);  
            }else{
                $sql=Db::table($country)->alias('o')->where("o.code_sku",'in',$countryArr);  
            }
        }else if(empty($countryArr) && empty($shopArr)){    //国家仓出库和店仓出库都不存在
            if(!empty($purchaseArr)){
                $sql=Db::table($purchase)->alias('c')->union($purchase)->where("c.code_sku",'in',$purchaseArr);  
            }
            if(!empty($param['down'])&&$param['down']==1){
                return ["code" => "500", "msg" => "导出数据为空"];
             }
             return ["code"=>1,"count"=>0,"data"=>[]];
        }
        $totalObj=clone $sql;
        $totalSql=$totalObj->buildSql();
        $total=Db::table($totalSql)->alias('a')->count();
        $param['total']=$total;
        return \app\common\service\ToolService::downOrReturn($sql, $param, $titles,$function);
     }
 }

